﻿using NLog;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using WTAppletProjectHTTPS.Models;

namespace WTAppletProjectHTTPS.Controllers
{

    public class ColaProductionFullInspectionRecordQueryController : ApiController
    {
        private static readonly Logger Logger = NLog.LogManager.GetCurrentClassLogger();

        // POST api/<controller>
        public async Task<HttpResponseMessage> Post([FromBody] ColaProductionFullInspectionRecordQuery CPFIRQ)
        {
            ReturnObjectQuery RO = new ReturnObjectQuery();
            if (CPFIRQ.PageNumber <= 0 || CPFIRQ.PageSize <= 0)
            {

                RO.Status = HttpStatusCode.BadRequest;
                RO.Message = "分页参数无效";
                RO.Data = null;
                Logger.Error("分页参数无效");
                return Request.CreateResponse(HttpStatusCode.BadRequest, RO);
            }

            try
            {
                using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
                {
                    await connection.OpenAsync();

                    var whereClause = "WHERE 1=1";
                    if (CPFIRQ.CPFIR_ID != 0)
                    {
                        whereClause += " AND CPFIRID = @ID";
                    }
                    if (CPFIRQ.CPFIR_ProductionDate.HasValue)
                    {
                        whereClause += " AND CPFIRProductionDate = @ProductionDate";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_LineParting))
                    {
                        whereClause += " AND CPFIRLineParting LIKE @LineParting";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_ProductionModel))
                    {
                        whereClause += " AND CPFIRProductionModel LIKE @ProductionModel";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_OrderNumber))
                    {
                        whereClause += " AND CPFIROrderNumber LIKE @OrderNumber";
                    }
                    if (CPFIRQ.CPFIR_ProductionQuantity > 0)
                    {
                        whereClause += " AND CPFIRProductionQuantity = @ProductionQuantity";
                    }
                    if (CPFIRQ.CPFIR_DefectiveQuantity > 0)
                    {
                        whereClause += " AND CPFIRDefectiveQuantity = @DefectiveQuantity";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_BrokenFoot))
                    {
                        whereClause += " AND CPFIRBrokenFoot LIKE @BrokenFoot";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_MixedMaterials))
                    {
                        whereClause += " AND CPFIRMixedMaterials LIKE @MixedMaterials";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_LaserMarkingDefect))
                    {
                        whereClause += " AND CPFIRLaserMarkingDefect LIKE @LaserMarkingDefect";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_MissingGasket))
                    {
                        whereClause += " AND CPFIRMissingGasket LIKE @MissingGasket";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_PoorHandfeel))
                    {
                        whereClause += " AND CPFIRPoorHandfeel LIKE @PoorHandfeel";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_FinishedProductAppearance))
                    {
                        whereClause += " AND CPFIRFinishedProductAppearance LIKE @FinishedProductAppearance";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_MaterialAppearanceDefect))
                    {
                        whereClause += " AND CPFIRMaterialAppearanceDefect LIKE @MaterialAppearanceDefect";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_SprayPaintAppearanceDefect))
                    {
                        whereClause += " AND CPFIRSprayPaintAppearanceDefect LIKE @SprayPaintAppearanceDefect";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_DisconnectionDefect))
                    {
                        whereClause += " AND CPFIRDisconnectionDefect LIKE @DisconnectionDefect";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_PackagingDefect))
                    {
                        whereClause += " AND CPFIRPackagingDefect LIKE @PackagingDefect";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_AssemblyDefect))
                    {
                        whereClause += " AND CPFIRAssemblyDefect LIKE @AssemblyDefect";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_ProtectiveDoorNotRebound))
                    {
                        whereClause += " AND CPFIRProtectiveDoorNotRebound LIKE @ProtectiveDoorNotRebound";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_PlugNotFullyInserted))
                    {
                        whereClause += " AND CPFIRPlugNotFullyInserted LIKE @PlugNotFullyInserted";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_VoltageResistanceDefect))
                    {
                        whereClause += " AND CPFIRVoltageResistanceDefect LIKE @VoltageResistanceDefect";
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_MarkingMismatch))
                    {
                        whereClause += " AND CPFIRMarkingMismatch LIKE @MarkingMismatch";
                    }

                    var query = $@"
                        SELECT 
                            CPFIRID, CPFIRProductionDate, CPFIRLineParting, CPFIRProductionModel, 
                            CPFIROrderNumber, CPFIRProductionQuantity, CPFIRDefectiveQuantity, CPFIRBrokenFoot, 
                            CPFIRMixedMaterials, CPFIRLaserMarkingDefect, CPFIRMissingGasket, CPFIRPoorHandfeel, 
                            CPFIRFinishedProductAppearance, CPFIRMaterialAppearanceDefect, CPFIRSprayPaintAppearanceDefect, 
                            CPFIRDisconnectionDefect, CPFIRPackagingDefect, CPFIRAssemblyDefect, 
                            CPFIRProtectiveDoorNotRebound, CPFIRPlugNotFullyInserted, CPFIRVoltageResistanceDefect, 
                            CPFIRMarkingMismatch,CPFIRCreateCreator,CPFIRCreateTime, COUNT(*) OVER() AS TotalCount
                        FROM 
                            (SELECT 
                                CPFIRID, CPFIRProductionDate, CPFIRLineParting, CPFIRProductionModel, 
                                CPFIROrderNumber, CPFIRProductionQuantity, CPFIRDefectiveQuantity, CPFIRBrokenFoot, 
                                CPFIRMixedMaterials, CPFIRLaserMarkingDefect, CPFIRMissingGasket, CPFIRPoorHandfeel, 
                                CPFIRFinishedProductAppearance, CPFIRMaterialAppearanceDefect, CPFIRSprayPaintAppearanceDefect, 
                                CPFIRDisconnectionDefect, CPFIRPackagingDefect, CPFIRAssemblyDefect, 
                                CPFIRProtectiveDoorNotRebound, CPFIRPlugNotFullyInserted, CPFIRVoltageResistanceDefect, 
                                CPFIRMarkingMismatch,CPFIRCreateCreator,CPFIRCreateTime, ROW_NUMBER() OVER(ORDER BY CPFIRID) AS RowNum
                             FROM ColaProductionFullInspectionRecord {whereClause}) AS Result
                        WHERE 
                            RowNum BETWEEN @StartRow AND @EndRow
                        ORDER BY RowNum";

                    int startRow = (CPFIRQ.PageNumber - 1) * CPFIRQ.PageSize + 1;
                    int endRow = CPFIRQ.PageNumber * CPFIRQ.PageSize;

                    var parameters = new List<SqlParameter>
                    {
                        new SqlParameter("@StartRow", SqlDbType.Int) { Value = startRow },
                        new SqlParameter("@EndRow", SqlDbType.Int) { Value = endRow }
                    };
                    if (CPFIRQ.CPFIR_ID != 0)
                    {
                        parameters.Add(new SqlParameter("@ID", SqlDbType.Int) { Value = CPFIRQ.CPFIR_ID });
                    }
                    if (CPFIRQ.CPFIR_ProductionDate.HasValue)
                    {
                        parameters.Add(new SqlParameter("@ProductionDate", SqlDbType.DateTime) { Value = CPFIRQ.CPFIR_ProductionDate.Value });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_LineParting))
                    {
                        parameters.Add(new SqlParameter("@LineParting", SqlDbType.NVarChar, 50) { Value = $"%{CPFIRQ.CPFIR_LineParting}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_ProductionModel))
                    {
                        parameters.Add(new SqlParameter("@ProductionModel", SqlDbType.NVarChar, 50) { Value = $"%{CPFIRQ.CPFIR_ProductionModel}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_OrderNumber))
                    {
                        parameters.Add(new SqlParameter("@OrderNumber", SqlDbType.NVarChar, 50) { Value = $"%{CPFIRQ.CPFIR_OrderNumber}%" });
                    }
                    if (CPFIRQ.CPFIR_ProductionQuantity > 0)
                    {
                        parameters.Add(new SqlParameter("@ProductionQuantity", SqlDbType.Int) { Value = CPFIRQ.CPFIR_ProductionQuantity });
                    }
                    if (CPFIRQ.CPFIR_DefectiveQuantity > 0)
                    {
                        parameters.Add(new SqlParameter("@DefectiveQuantity", SqlDbType.Int) { Value = CPFIRQ.CPFIR_DefectiveQuantity });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_BrokenFoot))
                    {
                        parameters.Add(new SqlParameter("@BrokenFoot", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_BrokenFoot}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_MixedMaterials))
                    {
                        parameters.Add(new SqlParameter("@MixedMaterials", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_MixedMaterials}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_LaserMarkingDefect))
                    {
                        parameters.Add(new SqlParameter("@LaserMarkingDefect", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_LaserMarkingDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_MissingGasket))
                    {
                        parameters.Add(new SqlParameter("@MissingGasket", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_MissingGasket}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_PoorHandfeel))
                    {
                        parameters.Add(new SqlParameter("@PoorHandfeel", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_PoorHandfeel}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_FinishedProductAppearance))
                    {
                        parameters.Add(new SqlParameter("@FinishedProductAppearance", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_FinishedProductAppearance}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_MaterialAppearanceDefect))
                    {
                        parameters.Add(new SqlParameter("@MaterialAppearanceDefect", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_MaterialAppearanceDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_SprayPaintAppearanceDefect))
                    {
                        parameters.Add(new SqlParameter("@SprayPaintAppearanceDefect", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_SprayPaintAppearanceDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_DisconnectionDefect))
                    {
                        parameters.Add(new SqlParameter("@DisconnectionDefect", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_DisconnectionDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_PackagingDefect))
                    {
                        parameters.Add(new SqlParameter("@PackagingDefect", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_PackagingDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_AssemblyDefect))
                    {
                        parameters.Add(new SqlParameter("@AssemblyDefect", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_AssemblyDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_ProtectiveDoorNotRebound))
                    {
                        parameters.Add(new SqlParameter("@ProtectiveDoorNotRebound", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_ProtectiveDoorNotRebound}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_PlugNotFullyInserted))
                    {
                        parameters.Add(new SqlParameter("@PlugNotFullyInserted", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_PlugNotFullyInserted}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_VoltageResistanceDefect))
                    {
                        parameters.Add(new SqlParameter("@VoltageResistanceDefect", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_VoltageResistanceDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CPFIRQ.CPFIR_MarkingMismatch))
                    {
                        parameters.Add(new SqlParameter("@MarkingMismatch", SqlDbType.NVarChar, 100) { Value = $"%{CPFIRQ.CPFIR_MarkingMismatch}%" });
                    }

                    using (var command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddRange(parameters.ToArray());
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (!reader.HasRows)
                            {
                                Logger.Warn("未找到记录");
                                RO.Status = HttpStatusCode.NotFound;
                                RO.Message = "未找到记录";
                                RO.Data = null;
                                return Request.CreateResponse(HttpStatusCode.NotFound, RO);
                            }

                            var products = new List<ColaProductionFullInspectionRecord>();
                            int totalCount = 0;
                            while (await reader.ReadAsync())
                            {
                                if (totalCount == 0)
                                {
                                    totalCount = reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }

                                products.Add(new ColaProductionFullInspectionRecord
                                {
                                    CPFIR_ID = reader.GetInt32(reader.GetOrdinal("CPFIRID")),
                                    CPFIR_ProductionDate = reader.GetDateTime(reader.GetOrdinal("CPFIRProductionDate")),
                                    CPFIR_LineParting = reader.GetString(reader.GetOrdinal("CPFIRLineParting")),
                                    CPFIR_ProductionModel = reader.GetString(reader.GetOrdinal("CPFIRProductionModel")),
                                    CPFIR_OrderNumber = reader.GetString(reader.GetOrdinal("CPFIROrderNumber")),
                                    CPFIR_ProductionQuantity = reader.GetInt32(reader.GetOrdinal("CPFIRProductionQuantity")),
                                    CPFIR_DefectiveQuantity = reader.GetInt32(reader.GetOrdinal("CPFIRDefectiveQuantity")),
                                    CPFIR_BrokenFoot = reader.GetString(reader.GetOrdinal("CPFIRBrokenFoot")),
                                    CPFIR_MixedMaterials = reader.GetString(reader.GetOrdinal("CPFIRMixedMaterials")),
                                    CPFIR_LaserMarkingDefect = reader.GetString(reader.GetOrdinal("CPFIRLaserMarkingDefect")),
                                    CPFIR_MissingGasket = reader.GetString(reader.GetOrdinal("CPFIRMissingGasket")),
                                    CPFIR_PoorHandfeel = reader.GetString(reader.GetOrdinal("CPFIRPoorHandfeel")),
                                    CPFIR_FinishedProductAppearance = reader.GetString(reader.GetOrdinal("CPFIRFinishedProductAppearance")),
                                    CPFIR_MaterialAppearanceDefect = reader.GetString(reader.GetOrdinal("CPFIRMaterialAppearanceDefect")),
                                    CPFIR_SprayPaintAppearanceDefect = reader.GetString(reader.GetOrdinal("CPFIRSprayPaintAppearanceDefect")),
                                    CPFIR_DisconnectionDefect = reader.GetString(reader.GetOrdinal("CPFIRDisconnectionDefect")),
                                    CPFIR_PackagingDefect = reader.GetString(reader.GetOrdinal("CPFIRPackagingDefect")),
                                    CPFIR_AssemblyDefect = reader.GetString(reader.GetOrdinal("CPFIRAssemblyDefect")),
                                    CPFIR_ProtectiveDoorNotRebound = reader.GetString(reader.GetOrdinal("CPFIRProtectiveDoorNotRebound")),
                                    CPFIR_PlugNotFullyInserted = reader.GetString(reader.GetOrdinal("CPFIRPlugNotFullyInserted")),
                                    CPFIR_VoltageResistanceDefect = reader.GetString(reader.GetOrdinal("CPFIRVoltageResistanceDefect")),
                                    CPFIR_MarkingMismatch = reader.GetString(reader.GetOrdinal("CPFIRMarkingMismatch")),
                                    CPFIR_CreateCreator = reader.GetString(reader.GetOrdinal("CPFIRCreateCreator")),
                                    CPFIR_CreateTime = reader.GetDateTime(reader.GetOrdinal("CPFIRCreateTime"))
                                });
                            }

                            Logger.Info("查询成功");
                            RO.Status = HttpStatusCode.OK;
                            RO.Message = "OK";
                            RO.Data = products;
                            RO.TotalCount = totalCount;
                            RO.PageNumber = CPFIRQ.PageNumber;
                            RO.PageSize = CPFIRQ.PageSize;
                            return Request.CreateResponse(HttpStatusCode.OK, RO);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error("查询失败: " + ex.Message);
                RO.Status = HttpStatusCode.InternalServerError;
                RO.Message = "Error: " + ex.Message;
                RO.Data = null;
                return Request.CreateResponse(HttpStatusCode.InternalServerError, RO);
            }
        }
    }
}
